﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.SqlClient;

namespace TB.DALSqlProvider
{
    /// <summary>
    /// 对*username*操作数据库(针对SQL Server)的实现
    /// </summary>
    public class usernameSqlDALProvider:IDAL.IDALusername
    { 
        public int Insert(TB.Entity.username obj)
        {
           //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_username_insert") as SqlCommand;
            //3.cmd参数赋值
            cmd.Parameters.AddWithValue("@nickname",obj.nickname==String.Empty?DBNull.Value:(object)obj.nickname);
            cmd.Parameters.AddWithValue("@pwd",obj.pwd==String.Empty?DBNull.Value:(object)obj.pwd);
            cmd.Parameters.AddWithValue("@hangye",obj.hangye==String.Empty?DBNull.Value:(object)obj.hangye);
            cmd.Parameters.AddWithValue("@startDate",obj.startDate==DateTime.Now?DBNull.Value:(object)obj.startDate);
            cmd.Parameters.AddWithValue("@endDate",obj.endDate==DateTime.Now?DBNull.Value:(object)obj.endDate);
            cmd.Parameters.AddWithValue("@startShopId",obj.startShopId==0?DBNull.Value:(object)obj.startShopId);
            cmd.Parameters.AddWithValue("@endShopId",obj.endShopId==0?DBNull.Value:(object)obj.endShopId);
            cmd.Parameters.AddWithValue("@reverse1",obj.reverse1==String.Empty?DBNull.Value:(object)obj.reverse1);
            cmd.Parameters.AddWithValue("@reverse2",obj.reverse2==String.Empty?DBNull.Value:(object)obj.reverse2);
            cmd.Parameters.AddWithValue("@reverse3",obj.reverse3==String.Empty?DBNull.Value:(object)obj.reverse3);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
           return db.ExecuteNonQuery(cmd);
        }
        
        public int Update(TB.Entity.username obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_username_update") as SqlCommand;
            //3.cmd参数赋值
            cmd.Parameters.AddWithValue("@nickname",obj.nickname==String.Empty?DBNull.Value:(object)obj.nickname);
            cmd.Parameters.AddWithValue("@pwd",obj.pwd==String.Empty?DBNull.Value:(object)obj.pwd);
            cmd.Parameters.AddWithValue("@hangye",obj.hangye==String.Empty?DBNull.Value:(object)obj.hangye);
            cmd.Parameters.AddWithValue("@startDate",obj.startDate==DateTime.Now?DBNull.Value:(object)obj.startDate);
            cmd.Parameters.AddWithValue("@endDate",obj.endDate==DateTime.Now?DBNull.Value:(object)obj.endDate);
            cmd.Parameters.AddWithValue("@startShopId",obj.startShopId==0?DBNull.Value:(object)obj.startShopId);
            cmd.Parameters.AddWithValue("@endShopId",obj.endShopId==0?DBNull.Value:(object)obj.endShopId);
            cmd.Parameters.AddWithValue("@reverse1",obj.reverse1==String.Empty?DBNull.Value:(object)obj.reverse1);
            cmd.Parameters.AddWithValue("@reverse2",obj.reverse2==String.Empty?DBNull.Value:(object)obj.reverse2);
            cmd.Parameters.AddWithValue("@reverse3",obj.reverse3==String.Empty?DBNull.Value:(object)obj.reverse3);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }
        
        public int Delete(TB.Entity.username obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_username_delete") as SqlCommand;
            //3.cmd参数赋值
           cmd.Parameters.AddWithValue("@nickname", obj.nickname);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }
        
        public List<TB.Entity.username> LoadAll()
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_username_loadAll") as SqlCommand;
            //3.通过db执行一个DBReader
            var reader =  db.ExecuteReader(cmd);
            //4.遍历DBReader,生成新的Entity对象,然后添加到List
            List<TB.Entity.username> list = new List<TB.Entity.username>();
            while (reader.Read())
            {
               list.Add(new TB.Entity.username()
               {
                   nickname = reader["nickname"] == DBNull.Value ? String.Empty : (string)reader["nickname"],
                   pwd = reader["pwd"] == DBNull.Value ? String.Empty : (string)reader["pwd"],
                   hangye = reader["hangye"] == DBNull.Value ? String.Empty : (string)reader["hangye"],
                   startDate = reader["startDate"] == DBNull.Value ? DateTime.Now : (DateTime)reader["startDate"],
                   endDate = reader["endDate"] == DBNull.Value ? DateTime.Now : (DateTime)reader["endDate"],
                   startShopId = reader["startShopId"] == DBNull.Value ? 0 : (int)reader["startShopId"],
                   endShopId = reader["endShopId"] == DBNull.Value ? 0 : (int)reader["endShopId"],
                   reverse1 = reader["reverse1"] == DBNull.Value ? String.Empty : (string)reader["reverse1"],
                   reverse2 = reader["reverse2"] == DBNull.Value ? String.Empty : (string)reader["reverse2"],
                   reverse3 = reader["reverse3"] == DBNull.Value ? String.Empty : (string)reader["reverse3"] 
               });
           }
           return list;
        }

        public TB.Entity.username LoadById(string nickname)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_username_loadById") as SqlCommand;
            //3.通过db执行一个DBReader
            cmd.Parameters.AddWithValue("@nickname", nickname);
            var reader = db.ExecuteReader(cmd);
            //4.遍历DBReader,生成一个新的Entity对象 
            TB.Entity.username entity = null;
            while (reader.Read())
            {
                entity = new TB.Entity.username()
                {
                    nickname = reader["nickname"] == DBNull.Value ? String.Empty : (string)reader["nickname"],
                    pwd = reader["pwd"] == DBNull.Value ? String.Empty : (string)reader["pwd"],
                    hangye = reader["hangye"] == DBNull.Value ? String.Empty : (string)reader["hangye"],
                    startDate = reader["startDate"] == DBNull.Value ? DateTime.Now : (DateTime)reader["startDate"],
                    endDate = reader["endDate"] == DBNull.Value ? DateTime.Now : (DateTime)reader["endDate"],
                    startShopId = reader["startShopId"] == DBNull.Value ? 0 : (int)reader["startShopId"],
                    endShopId = reader["endShopId"] == DBNull.Value ? 0 : (int)reader["endShopId"],
                    reverse1 = reader["reverse1"] == DBNull.Value ? String.Empty : (string)reader["reverse1"],
                    reverse2 = reader["reverse2"] == DBNull.Value ? String.Empty : (string)reader["reverse2"],
                    reverse3 = reader["reverse3"] == DBNull.Value ? String.Empty : (string)reader["reverse3"]
                };
            }
            return entity;
        }   
    }
}